Navigating the PostgreSQL Seas: A Quest To Hidden Treasures

PGConf.de
2024-04-12

Who am I

• Lætitia Avrot
• PostgreSQL Europe Treasurer
• #PostgresWomen Founder
• Practice Leader, Postgres & Security at EDB
• @l_avrot
• mydbanotebook.org / psql-tips.org

The treasure map

• Small gems
• Text Quoting
• Pattern matching
• Complex datatypes handling
• Upsert/MERGE

Small Gems

DDL is transactionnal

laetitia=# create table test(id integer generated always as identity);
CREATE TABLE

laetitia=# table test;
 id 
----
(0 rows) 

DDL is transactionnal

laetitia=# begin;
BEGIN

laetitia=*# drop table test;
DROP TABLE

laetitia=*# table test;
2024-04-11 13:05:20.589 CEST [18422] ERROR:  relation "test" does not exist at character 7
2024-04-11 13:05:20.589 CEST [18422] STATEMENT:  table test;
ERROR:  relation "test" does not exist
LINE 1: table test;
              ^

laetitia=!# rollback;
ROLLBACK

laetitia=# table test;
 id 
----
(0 rows)

Comments

COMMENT ON LANGUAGE plpython IS 'Python support for stored procedures';

COMMENT ON OPERATOR - (NONE, integer) IS 'Unary minus';

COMMENT ON DATABASE my_database IS 'Development Database';

COMMENT ON ROLE my_role IS 'Administration group for finance tables';

Specialized indexes

• multicolumns
• partial
• covering
• expression
• special ops
• b-tree, BRIN, hash, GiST, GIN, SP-GiST

Foreign Data Wrappers

Access ALL YOUR DATA (really, all your data, we are not joking) from EVERYWHERE to Postgres!

Server-side programming

• PL/Java
• PL/Python
• PL/R
• PL/pgSQL (like PL/SQL)
• PL/Ruby
• PL/Scheme
• PL/sh
• PL/Tcl
• PL/v8 (JavaScript)
• SPI (C)

Your precious ship: text quoting

Text quoting

• " (double quote) for identifiers
• ' (quote) for strings
• escape character: \
• escaping quotes: ''

Dollar-Quoted String

$$Dianne's horse$$

$SomeTag$Dianne's horse$SomeTag$

Your straw hat: text searching

Like and ilike

• like (with or without _ or %)
• ilike
• ~~ and ~~*
• !~~ and !~~*

Similar to

string SIMILAR TO pattern [ESCAPE escape-character]

string NOT SIMILAR TO pattern [ESCAPE escape-character]

Similar to

• | for alternation
• *, +, ?, {m,n} for repetition
• () for groups
• [] for a character class

Proper regular expressions

• ~ operator

  'thomas' ~ 't.*ma' → t

• ~* operator

  'thomas' ~ 't.*ma' → t

Proper regular expressions

• !~ operator

  'thomas' !~ 't.*max' → t

• !~* operator

  'thomas' !~* 'T.*ma' → f

Regular expression functions

Your crew: complex data types handling

JSON

SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
          

JSON path

• $.track.segments: retrieves the available track segments
• $.track.segments[*].location: retrieves the contents of an array
• $.track.segments[1].location: returns the coordinates of the first segment only
• ? (condition): filters

JSON Tables

• Query JSON data in SQL
• Not in Postgres 15
• Not in Postgres 16
• Hopefully in postgres 17 🤞

Geographical data

• PostGIS
• Mix of spatial queries and traditional SQL
• "What are the cities within 150 KM of Boston with the 10 lowest medium home prices?"

Geographical data

select name,
  medium_hval,
  location
from interesting_cities
where (
  select ST_Distance (
      ST_Transform (location, 3587),
      ST_Transform( (
        select location
        from interesting_cities
        where name = 'Boston'), 3587)
  ) ) < 150000
order by medium_hval
limit 10;

Geographical data

The one piece: Upserts/MERGE

Inserts tricks

• on conflict
• returning
• CTEs

Returning

WITH upd AS (
  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
    (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
    RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

Upsert

INSERT INTO distributors (did, dname)
    VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

Merge

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE;

Thank you!